package com.nondo.dean.product.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.session.RowBounds;
import org.springframework.stereotype.Component;

import com.nondo.dean.product.domain.Product;
import com.nondo.dean.product.domain.ProductCategory;
import com.nondo.dean.product.domain.ProductMix;
import com.nondo.dean.product.domain.Series;
import com.nondo.dean.product.model.param.ProductOrderParam;
import com.nondo.dean.product.model.param.ProductQueryParam;
import com.nondo.dean.product.model.param.PurchaseParam;
import com.nondo.dean.product.model.result.ProductResult;

/**
 * @author xiaofan
 * @Description:
 * @throws
 * @date 2018-04-0414:23
 * @Version
 */
@Component
public interface ProductExtendsMapper {
    /**
     * 修改库存列为0
     * @return
     */
    @Update("update product set in_the_inventory=0,reference_inventory=0 ")
    int updateInventoryAll();

    /**
     * 修改删除标示状态
     * @return
     */
    @Update("update product set del_flag=0 where del_flag=1 and (in_the_inventory > 0 or reference_inventory > 0) ")
    int updateDeleteStauts();
    
    /**
     * 按照商品名/系列/商品编码搜索产品列表条数统计
     * @param purchaseParam
     * @return
     */
    @Select({"SELECT COUNT(id) FROM (",
            "SELECT product.* FROM product product WHERE del_flag = 0 AND shelf_status=1 AND (product.`name` LIKE '%${name}%' OR product.`code` LIKE '%${name}%') UNION ",
            "SELECT product.* FROM product product INNER JOIN series series ON product.series_id = series.id WHERE product.del_flag = 0 AND series.del_flag = 0 AND shelf_status=1 AND series.`name` LIKE '%${name}%') id"})
    long countByExample(PurchaseParam purchaseParam);
    
    /**
     * 按照商品名/系列/商品编码搜索产品列表
     * @param purchaseParam
     * @param rowBounds
     * @return
     */
    @Select({"SELECT product.* FROM product product WHERE del_flag = 0 AND shelf_status=1 AND (product.`name` LIKE '%${name}%' OR product.`code` LIKE '%${name}%') UNION ",
            "SELECT product.* FROM product product INNER JOIN series series ON product.series_id = series.id WHERE product.del_flag = 0 AND series.del_flag = 0 AND shelf_status=1 AND series.`name` LIKE '%${name}%'"})
    List<Product> selectByExampleWithRowbounds(PurchaseParam purchaseParam, RowBounds rowBounds);
    
    /**
     * 减库存（防超卖）加销量
     * @param productId 产品ID
     * @param purchaseNum 采购产品数量
     * @return 影响行数
     */
    @Update("UPDATE product SET STOCK=STOCK-#{purchaseNum,jdbcType=INTEGER},SALE_NUM=SALE_NUM+#{purchaseNum,jdbcType=INTEGER} WHERE DEL_FLAG=0 AND STOCK>#{purchaseNum,jdbcType=INTEGER} AND ID=#{productId,jdbcType=INTEGER}")
    int updateStockSaleNum(ProductOrderParam orderParam);


    /**删除bom表全部数据
     * This method was generated by MyBatis Generator.
     * This method Remove all data from the product bom table
     *
     * @mbg.generated Fri Mar 30 17:07:11 CST 2018
     */
    @Select("truncate table product_bom")
    void delBom();


    /**
     * 筛选分类下没有产品的 分类
     * @return
     */
    @Select("SELECT * FROM product_category WHERE id IN (SELECT category_id FROM product WHERE del_flag<>1 GROUP BY category_id HAVING category_id<>0) AND del_flag<>1")
    List<ProductCategory> queryProductCategory();

    /**
     * 筛选系列下没有产品的 系列 总数统计
     * @return
     */
    @Select("SELECT count(*) FROM series WHERE id IN (SELECT series_id FROM product WHERE del_flag<>1 GROUP BY series_id) AND del_flag<>1")
    Long countBySeries();

    /**
     * 筛选系列下没有产品的 系列
     * @return
     */
    @Select("SELECT * FROM series WHERE id IN (SELECT series_id FROM product WHERE del_flag<>1 GROUP BY series_id) AND del_flag<>1 ORDER BY series.create_date desc")
    List<Series> selectBySeriesWithRowbounds(RowBounds rowBounds);

    /**
     * 筛选系列下没有产品的 系列 不带分页
     * @return
     */
    @Select("SELECT * FROM series WHERE id IN (SELECT series_id FROM product WHERE category_id=#{categoryId,jdbcType=INTEGER} AND del_flag<>1 AND shelf_status=1 GROUP BY series_id) AND del_flag<>1")
    List<Series> selectSeriesAll(@Param("categoryId") Integer categoryId);

    /**
     * 筛选系列下的分类
     * @param seriesId
     * @return
     */
    @Select("SELECT * FROM product_category WHERE id IN (SELECT category_id FROM product WHERE del_flag<>1 AND series_id=#{seriesId,jdbcType=INTEGER} AND shelf_status=1 GROUP BY category_id HAVING category_id<>0) AND del_flag<>1")
    List<ProductCategory>  queryCategory(@Param("seriesId") Integer seriesId);


    /**
     * 系列下的 分类 产品总数统计
     * @param ids
     * @param seriesId
     * @return
     */
    @Select("select count(*) from product where del_flag=0 and shelf_status=1 and series_id=#{seriesId,jdbcType=INTEGER}  and category_id in (${ids})")
    Long countSeriesCategory(@Param("ids") String ids,@Param("seriesId") Integer seriesId);

    /**
     * 查找产品所参与的产品组合的产品列表
     * @param productId
     * @return
     */
    @Select("SELECT * FROM product_mix WHERE del_flag=0 AND shelf_status=1 AND FIND_IN_SET(#{productId,jdbcType=INTEGER},product_ids)")
    List<ProductMix> getProductMixListByProductId(@Param("productId") Integer productId);

    /**
     * 查看某个产品的子件产品
     * @param productParam
     * @return
     */
    @SelectProvider(type = ProductExtendsSqlProvider.class, method = "selectChildListByCode")
    List<ProductResult> getChildListByCode(ProductQueryParam productParam);

}
